{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "###########调包\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import *\n",
    "import time\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "############数据文件文件路径\n",
    "train_dir = '../../contest/train/'\n",
    "B_dir = '../../contest/B榜/'\n",
    "train_pickle_dir = './pickle/train/'\n",
    "B_pickle_dir = './pickle/B/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_notzero(series_x):\n",
    "    mode = series_x[(series_x > 0)]\n",
    "    return mode.count()\n",
    "\n",
    "def count_zero(series_x):\n",
    "    mode = series_x[(series_x == 0)]\n",
    "    return mode.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工企业中收():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            企业中收_T0 = pd.read_csv(os.path.join(data_dir,'XW_MBINCM_SUM.csv'))  \n",
    "        else:\n",
    "            企业中收_T0 = pd.read_csv(os.path.join(data_dir,'XW_MBINCM_SUM_B.csv'))\n",
    "        企业中收_T0.columns = ['数据日期','客户ID','本月中收人民币','本月中收笔数']\n",
    "\t\t\n",
    "        企业中收_T0['本月中收人民币'] = pow((企业中收_T0['本月中收人民币'])/3.12,3).round(2)\n",
    "\n",
    "        企业中收_T0['数据日期'] = 企业中收_T0['数据日期'].astype('str')\n",
    "        企业中收_T0['数据日期'] = 企业中收_T0['数据日期'].astype('datetime64[ns]')\n",
    "        企业中收_T0['数据日期']=pd.to_datetime(企业中收_T0['数据日期'])+pd.DateOffset(days=11886)\n",
    "        企业中收_T0.sort_values(['客户ID','数据日期'],inplace=True,ascending=True)\n",
    "\t\t\n",
    "        企业中收_T0['本月中收笔数1']=np.where(企业中收_T0['本月中收人民币']>0,企业中收_T0['本月中收笔数'],0)  \n",
    "\n",
    "        企业中收_T1=企业中收_T0.groupby(['客户ID','数据日期']).agg({'本月中收人民币':['sum',count_notzero],'本月中收笔数1':['sum']})\n",
    "        企业中收_T1.reset_index(inplace=True)\n",
    "        企业中收_T1.columns = ['客户ID','数据日期','本月中收人民币','有收入账户数','本月中收笔数']\n",
    "        企业中收_T1['近6月平均值']=企业中收_T1.groupby(['客户ID']).本月中收人民币.apply(lambda x:x.rolling(window=6,min_periods=6).mean().round(1)) \n",
    "        企业中收_T1['近12月平均值']=企业中收_T1.groupby(['客户ID']).本月中收人民币.apply(lambda x:x.rolling(window=12,min_periods=12).mean().round(1)) \n",
    "\n",
    "        企业中收_T2=企业中收_T1.groupby(['客户ID']).agg({'近6月平均值':['last'],'近12月平均值':['last']\\\n",
    "\t\t,'本月中收人民币':['std','mean',count_zero],'数据日期':['count']})\n",
    "        企业中收_T2.reset_index(inplace=True)\n",
    "        企业中收_T2.columns = ['客户ID','近3月中收平均值','近12月中收平均值','中收_std','中收_mean','无中收期数','中收期数']\n",
    "\n",
    "        企业中收_T2['近3月中收变化']=(企业中收_T2['近3月中收平均值']/企业中收_T2['近12月中收平均值']).round(2)\n",
    "        企业中收_T2['中收空档期']=(企业中收_T2['无中收期数']/企业中收_T2['中收期数']).round(2)\n",
    "        企业中收_T2['企业中收_标准偏差'] = (100*企业中收_T2['中收_std']/企业中收_T2['中收_mean']).round(0)\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        中收特征=目标客户列表.merge(企业中收_T2,on=['客户ID'],how='left')\n",
    "        中收特征.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(中收特征, open(pickle_dir+'中收特征.p', 'wb'))\n",
    "        res.append(中收特征)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-4-bf7d00b76727>:22: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  企业中收_T1['近6月平均值']=企业中收_T1.groupby(['客户ID']).本月中收人民币.apply(lambda x:x.rolling(window=6,min_periods=6).mean().round(1))\n",
      "<ipython-input-4-bf7d00b76727>:23: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  企业中收_T1['近12月平均值']=企业中收_T1.groupby(['客户ID']).本月中收人民币.apply(lambda x:x.rolling(window=12,min_periods=12).mean().round(1))\n",
      "<ipython-input-4-bf7d00b76727>:22: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  企业中收_T1['近6月平均值']=企业中收_T1.groupby(['客户ID']).本月中收人民币.apply(lambda x:x.rolling(window=6,min_periods=6).mean().round(1))\n",
      "<ipython-input-4-bf7d00b76727>:23: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  企业中收_T1['近12月平均值']=企业中收_T1.groupby(['客户ID']).本月中收人民币.apply(lambda x:x.rolling(window=12,min_periods=12).mean().round(1))\n"
     ]
    }
   ],
   "source": [
    "企业中收_训练集,企业中收_测试集=加工企业中收()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 10)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "企业中收_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 10)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "企业中收_测试集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#加工企业中间业务表训练集、测试集\n",
    "def 加工企业中间业务特征补充():\n",
    "    res = [] \n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            中收_T0 = pd.read_csv(os.path.join(data_dir,'XW_MBINCM_SUM.csv')) \n",
    "        else:\n",
    "            中收_T0 = pd.read_csv(os.path.join(data_dir,'XW_MBINCM_SUM_B.csv')) \n",
    "\n",
    "        中收_T0.columns = ['数据日期','客户ID','本月中收人民币','本月中收笔数']\n",
    "        中收_T0.sort_values(['客户ID','数据日期'])\n",
    "\n",
    "        中收_T0['数据日期']= 中收_T0['数据日期'].astype('str')\n",
    "        中收_T0['数据日期'] = 中收_T0['数据日期'].astype('datetime64[ns]')\n",
    "        中收_T0['数据日期']=pd.to_datetime(中收_T0['数据日期'])+pd.DateOffset(days=11886)\n",
    "\n",
    "        中收_T0['本月中收人民币'] = pow((中收_T0['本月中收人民币'])/3.12,3)\n",
    "        \n",
    "        中收_T1=中收_T0.groupby(['客户ID']).agg({'本月中收人民币':['sum','count','mean']})\n",
    "        中收_T1.reset_index(inplace=True)\n",
    "        中收_T1.columns = ['客户ID','中收近12个月资金额','中收去躁后近12个月的交易笔数','中收mean']\n",
    "\n",
    "        中收_T2=中收_T0.groupby(['客户ID','数据日期']).agg({'本月中收人民币':['sum'],'本月中收笔数':['sum']})\n",
    "        中收_T2.reset_index(inplace=True)\n",
    "        中收_T2.columns = ['客户ID','数据日期' ,'本月中收总人民币','本月中收总笔数']\n",
    "\n",
    "        中收_T3=中收_T2.groupby(['客户ID']).agg({'本月中收总人民币':['min','std','mean'],'本月中收总笔数':['min','std','mean']})\n",
    "        中收_T3.reset_index(inplace=True)\n",
    "        中收_T3.columns = ['客户ID','最小本月中收总人民币','本月中收总人民币_std','本月中收总人民币_mean',\\\n",
    "                                 '最小本月中收总笔数','本月中收总笔数_std','本月中收总笔数_mean']\n",
    "        \n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "        \n",
    "        企业中间业务特征=目标客户列表.merge(中收_T3,on=['客户ID'],how='left')\n",
    "        企业中间业务特征=企业中间业务特征.merge(中收_T1,on=['客户ID'],how='left')\n",
    "        企业中间业务特征.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "        pickle.dump(企业中间业务特征, open(pickle_dir+'Z企业中间业务特征.p', 'wb'))\n",
    "\n",
    "        res.append(企业中间业务特征)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "企业中间业务特征_训练集,企业中间业务特征_测试集= 加工企业中间业务特征补充()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
